import pymysql
import json
import sys

sys.path.append("resource")
import pandas as pd
import numpy as np
import os


# db操作类，包含基本的增删查改
class db_Operation:
    # 数据库初始化，登录数据库
    def __init__(self):
        # with open("../backEnd/resource/db_config.json", "rt") as f:
        current_path = os.path.dirname(__file__)
        with open(current_path + "\\db_config.json", "rt") as f:
            dic = json.load(f)

        try:
            self.connect = pymysql.connect \
                    (
                    host=dic['host'],  # 将字典中host主键对应的值赋给host 下面的同理
                    user=dic['user'],
                    password=dic['password'],
                    charset='utf8',
                    port=3306,  # mysql默认端口号
                    db=dic['database']  # 数据库
                )
            print("connect successfully")
        except Exception as e:
            print(e)

    # 数据库退出登录
    def close(self):
        self.connect.close()

    # 插入操作 参数：表名， 插入数据的dataframe格式
    def insert(self, table_name, df):
        # 判断df的类型
        try:
            if not (type(df) == pd.core.frame.DataFrame):
                raise TypeError(f'The type of arg \'df\' should be dataframe but {type(df)}.')
        except Exception as e:
            print(e)
            return -1, e
        c_name = str(df.columns.tolist()).replace("'", "").replace("[", '').replace("]", '').replace(" ", "")
        c_type = str(['%s' for i in range(len(df.columns.tolist()))]).replace("'", "").replace("[", '').replace("]",
                                                                                                                '').replace(
            " ", "")
        df = [tuple(df.iloc[i]) for i in range(df.shape[0])]
        sql = "insert into " + str(table_name) + "(" + str(c_name) + ") Values(" + str(c_type) + ");"
        cursor = self.connect.cursor()
        try:
            cursor.executemany(sql, df)
            self.connect.commit()
            cursor.close()
            print('Insert successfully!')
            return 1, "Insert successfully"
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            return -1, e

    # 删除操作 参数：表名，条件名，条件值
    def delete(self, table_name, key_name, key):
        # 判断key_name和key是否长度一致
        try:
            if not (len(key_name) == len(key)):
                raise ValueError(
                    f"The len of \'key_name\'({len(key_name)}) does not match the len of \'key\'({len(key)})")
        except Exception as e:
            print(e)
            # showWarningMessage(e)
            return -1, e

        sql = 'delete from ' + str(table_name) + ' where'
        for i, (kn, k) in enumerate(zip(key_name, key)):
            if type(k) == str:
                sql = sql + ' ' + str(kn) + ' = ' + "'" + str(k) + "'"
            else:
                sql = sql + ' ' + str(kn) + ' = ' + str(k)
            if not (i == len(key_name) - 1):
                sql = sql + ' and'
        # sql = sql + " on delete cascade"
        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            self.connect.commit()
            cursor.close()
            print('Delete successfully!')
            return 1, "Delete successfully!"
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            # showSqlWarningMessage(sql, e)
            return -1, e

    # 查找操作 参数：表名，条件名，条件值
    def select(self, table_name, key_name, key):
        # 判断key_name和key是否长度一致
        try:
            if not (len(key_name) == len(key)):
                raise ValueError(
                    f"The len of \'key_name\'({len(key_name)}) does not match the len of \'key\'({len(key)})")
        except Exception as e:
            print(e)
            # showWarningMessage(e)
            return -1, e

        sql = 'select * from ' + str(table_name) + ' where'
        for i, (kn, k) in enumerate(zip(key_name, key)):
            if type(k) == str:
                sql = sql + ' ' + str(kn) + ' = ' + "'" + str(k) + "'"
            else:
                sql = sql + ' ' + str(kn) + ' = ' + str(k)
            if not (i == len(key_name) - 1):
                sql = sql + ' and'
        # sql = 'select ifnull(('+sql+'),0) as result;'

        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            tuble = cursor.fetchall()
            cursor.execute('desc ' + table_name)
            c_name = cursor.fetchall()

            data = np.asarray(tuble)
            cn = []
            for i in c_name:
                cn.append(i[0])
            df = pd.DataFrame(data, columns=cn)
            cursor.close()
            print('Select successfully!')
            return 1, df
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            # showSqlWarningMessage(sql, e)
            # print("1231")
            return -1, e

    # 查找操作 参数：表名
    def selectAll(self, table_name):
        sql = 'select * from ' + str(table_name) + ';'
        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            tuble = cursor.fetchall()
            cursor.execute('desc ' + table_name)
            c_name = cursor.fetchall()
            data = np.asarray(tuble)
            cn = []
            for i in c_name:
                cn.append(i[0])
            df = pd.DataFrame(data, columns=cn)
            cursor.close()
            print('Select successfully!')
            return 1, df
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            return -1, e

    # 分页查找:
    # 参数（参数名  类型）：表名 string ，条件名与条件值list，排序  list      页数 int  偏移量 int
    # 返回： dataframe
    def selectPage(self, table_name, key_name, key, orderByList, limitNum, offsetNum):
        # 判断key_name和key是否长度一致
        try:
            if not (len(key_name) == len(key)):
                raise ValueError(
                    f"The len of \'key_name\'({len(key_name)}) does not match the len of \'key\'({len(key)})")
        except Exception as e:
            print(e)
            # showWarningMessage(e)
            return -1, e

        sql = 'SELECT* FROM ' + str(table_name) + ' WHERE'
        for i, (kn, k) in enumerate(zip(key_name, key)):
            if type(k) == str:
                sql = sql + ' ' + str(kn) + ' = ' + "'" + str(k) + "'"
            else:
                sql = sql + ' ' + str(kn) + ' = ' + str(k)
            if not (i == len(key_name) - 1):
                sql = sql + ' and'
        sql = sql + ' ORDER BY '
        for i in range(len(orderByList)):
            sql += str(orderByList[0]) + ' '
            if i != (len(orderByList) - 1):
                sql += ', '
        sql = sql + ' LIMIT ' + str(limitNum) + ' OFFSET ' + str(offsetNum) + ' ;'

        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            tuble = cursor.fetchall()
            cursor.execute('desc ' + table_name)
            c_name = cursor.fetchall()
            data = np.asarray(tuble)
            cn = []
            for i in c_name:
                cn.append(i[0])
            df = pd.DataFrame(data, columns=cn)
            cursor.close()
            print('Select successfully!')
            return 1, df
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            # showSqlWarningMessage(sql, e)
            return -1, e

    def selectAllPage(self, table_name, orderByList, limitNum, offsetNum):
        sql = 'SELECT* FROM ' + str(table_name)
        sql = sql + ' ORDER BY '
        for i in range(len(orderByList)):
            sql += str(orderByList[0]) + ' '
            if i != (len(orderByList) - 1):
                sql += ', '
        sql = sql + ' LIMIT ' + str(limitNum) + ' OFFSET ' + str(offsetNum) + ' ;'
        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            tuble = cursor.fetchall()
            cursor.execute('desc ' + table_name)
            c_name = cursor.fetchall()
            data = np.asarray(tuble)
            cn = []
            for i in c_name:
                cn.append(i[0])
            df = pd.DataFrame(data, columns=cn)
            cursor.close()
            print('Select successfully!')
            return 1, df
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            # showSqlWarningMessage(sql, e)
            return -1, e

    # 查找操作范围 参数：表名，条件名，符号，条件值
    def selectRange(self, table_name, key_name, key_cmp, key):
        # 判断key_name和key是否长度一致
        try:
            if not (len(key_name) == len(key)):
                raise ValueError(
                    f"The len of \'key_name\'({len(key_name)}) does not match the len of \'key\'({len(key)})")
        except Exception as e:
            print(e)
            # showWarningMessage(e)
            return -1, e

        sql = 'select * from ' + str(table_name) + ' where'
        for i, (kn, kcmp, k) in enumerate(zip(key_name, key_cmp, key)):
            if type(k) == str:
                sql = sql + ' ' + str(kn) + ' ' + str(kcmp) + ' ' + "'" + str(k) + "'"
            else:
                sql = sql + ' ' + str(kn) + ' ' + str(kcmp) + ' ' + str(k)
            if not (i == len(key_name) - 1):
                sql = sql + ' and'
        # sql = 'select ifnull(('+sql+'),0) as result;'

        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            tuble = cursor.fetchall()
            cursor.execute('desc ' + table_name)
            c_name = cursor.fetchall()

            data = np.asarray(tuble)
            cn = []
            for i in c_name:
                cn.append(i[0])
            df = pd.DataFrame(data, columns=cn)
            cursor.close()
            print('Select successfully!')
            return 1, df
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            # showSqlWarningMessage(sql, e)
            # print("1231")
            return -1, e

    # 修改操作 参数：表名，条件名，条件值，修改数据的data
    def update(self, table_name, key_name, key, data):
        # 判断data的类型
        try:
            if not (isinstance(data, dict)):
                raise TypeError(f'The type of arg \'df\' should be dict but {type(data)}.')
        except Exception as e:
            print(e)
            # showWarningMessage(e)
            return -1, e

        # 判断key_name和key是否长度一致
        try:
            if not (len(key_name) == len(key)):
                raise ValueError(
                    f"The len of \'key_name\'({len(key_name)}) does not match the len of \'key\'({len(key)})")
        except Exception as e:
            print(e)
            # showWarningMessage(e)
            return -1, e

        sql = 'update ' + str(table_name) + ' set'
        for i, j in enumerate(data):
            if type(data[j]) == str:
                sql = sql + ' ' + str(j) + ' = ' + "'" + str(data[j]) + "'"
            else:
                sql = sql + ' ' + str(j) + ' = ' + str(data[j])
            if not (i == len(data) - 1):
                sql = sql + ', '

        sql = sql + ' where'
        for i, (kn, k) in enumerate(zip(key_name, key)):
            if type(k) == str:
                sql = sql + ' ' + str(kn) + ' = ' + "'" + str(k) + "'"
            else:
                sql = sql + ' ' + str(kn) + ' = ' + str(k)
            if not (i == len(key_name) - 1):
                sql = sql + ' and'

        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            self.connect.commit()
            cursor.close()
            print('Update successfully!')
            return 1, "success"
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            return -1, e

    def select2Page(self, table_name, table_name_2, orderByList, limitNum, offsetNum):
        sql = 'SELECT* FROM ' + str(table_name) + ' UNION ALL SELECT* FROM ' + str(table_name_2)
        sql = sql + ' ORDER BY '
        for i in range(len(orderByList)):
            sql += str(orderByList[0]) + ' '
            if i != (len(orderByList) - 1):
                sql += ', '
        sql = sql + ' DESC LIMIT ' + str(limitNum) + ' OFFSET ' + str(offsetNum) + ' ;'
        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            tuble = cursor.fetchall()
            cursor.execute('desc ' + table_name)
            c_name = cursor.fetchall()
            data = np.asarray(tuble)
            cn = []
            for i in c_name:
                cn.append(i[0])
            df = pd.DataFrame(data, columns=cn)
            cursor.close()
            print('Select successfully!')
            return 1, df
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            # showSqlWarningMessage(sql, e)
            return -1, e

    def select2(self, table_name, table_name_2, key_name, key):
        # 判断key_name和key是否长度一致
        try:
            if not (len(key_name) == len(key)):
                raise ValueError(
                    f"The len of \'key_name\'({len(key_name)}) does not match the len of \'key\'({len(key)})")
        except Exception as e:
            print(e)
            # showWarningMessage(e)
            return -1, e

        sql = 'select * from ' + str(table_name) + ' where'
        for i, (kn, k) in enumerate(zip(key_name, key)):
            if type(k) == str:
                sql = sql + ' ' + str(kn) + ' = ' + "'" + str(k) + "'"
            else:
                sql = sql + ' ' + str(kn) + ' = ' + str(k)
            if not (i == len(key_name) - 1):
                sql = sql + ' and'
        # sql = 'select ifnull(('+sql+'),0) as result;'
        sql = sql + ' UNION ALL select * from ' + str(table_name_2) + ' where'
        for i, (kn, k) in enumerate(zip(key_name, key)):
            if type(k) == str:
                sql = sql + ' ' + str(kn) + ' = ' + "'" + str(k) + "'"
            else:
                sql = sql + ' ' + str(kn) + ' = ' + str(k)
            if not (i == len(key_name) - 1):
                sql = sql + ' and'

        cursor = self.connect.cursor()
        try:
            cursor.execute(sql)
            tuble = cursor.fetchall()
            cursor.execute('desc ' + table_name)
            c_name = cursor.fetchall()

            data = np.asarray(tuble)
            cn = []
            for i in c_name:
                cn.append(i[0])
            df = pd.DataFrame(data, columns=cn)
            cursor.close()
            print('Select successfully!')
            return 1, df
        except Exception as e:
            self.connect.rollback()
            cursor.close()
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            return -1, e

    def selectDate(self):
        pass
